package com.dmx.fw.util;

/**
 * 从Excel文件读取数据的基本实现类
 * @author stxidt(meitao)
 */

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelToData {

    private List fieldValueList;//表内容(每行一个Map<field,value>)

    private List readColList;//要读取的列的描述

    private int fieldRowIndex=0;//表头行位置

    private int readColSize;//要读音取的列数

    private byte[] buff;//文件缓存�ļ�����

    private HSSFWorkbook workbook;

    private HSSFSheet sheet;

    private int sheetAt=0;

    public ExcelToData() {
    }

    public ExcelToData(byte[] buff,List readColList)throws IOException{
        this.buff=buff;
        this.readColList=readColList;
        if(readColList!=null){
            this.readColSize=readColList.size();
        }
        boolean isSuccess=this.initializeWorkbook();
        if(!isSuccess){
            throw new IOException("--initialize content failure!--");
        }
    }

    public boolean setContent(byte[] buff)throws IOException{
        this.buff=buff;
        this.sheetAt=0;
        boolean isSuccess=this.initializeWorkbook();
        if(!isSuccess){
            throw new IOException("--initialize content failure!--");
        }
        return isSuccess;
    }

    public void setReadCol(List readColList){
        this.readColList=readColList;
        if(readColList!=null){
            this.readColSize=readColList.size();
        }
    }
    public void setReadCol(List readColList,int fieldIndex){
        this.setReadCol(readColList);
        this.fieldRowIndex=fieldIndex;
    }


    public void setReadColSize(int size){
        this.readColSize=size;
    }

    public void setSheetAt(int index)throws IOException{
        if(index<0){
            throw new IOException("--index under zero!--");
        }
        this.sheetAt=index;
        if(this.workbook==null){
            throw new IOException("--the content of workbook is null!--");
        }
        this.sheet=this.workbook.getSheetAt(index);
        if(this.sheet==null){
            throw new IOException("--setting sheet failure!--");
        }
    }

    public int getSheetAt(){
        return this.sheetAt;
    }

    public List getDataList()throws IOException{
        if(this.workbook==null||this.sheet==null){
            throw new IOException("--the content of workbook is null!--");
        }
        short[] cols=this.getReadColNum();//获得要读的列
        if(cols==null)return null;
        this.parseData(cols);//开始解析��ʼ����
        return this.fieldValueList;
    }

    private boolean initializeWorkbook(){
        if(this.buff==null||this.buff.length==0){
            return false;
        }
        ByteArrayInputStream bais=new ByteArrayInputStream(this.buff);
        try{
            this.workbook = new HSSFWorkbook(bais);
            this.sheet=this.workbook.getSheetAt(this.sheetAt);
        }catch(Exception e){
            return false;
        }
        if(this.workbook==null||this.sheet==null){
            return false;
        }
        return true;
    }

    private void parseData(short[] cols){
        int rowSize=sheet.getLastRowNum();
        HSSFRow row=null;
        this.fieldValueList=new ArrayList(); //存放所有行结果
        for(int i=(this.fieldRowIndex+1);i<=rowSize;i++){
            row=this.getRow(i); //获得一行
            if(row!=null){
                Map rowMap=new HashMap(); //存放所有列结果
                for(int j=0;j<cols.length;j++){
                    if(cols[j]!=-1){
                        HSSFCell cell=row.getCell(cols[j]); //获得相应的列
                        if(cell!=null){
                            rowMap.put(this.readColList.get(j),this.getCellValue(cell)); //列值
                        }else{
                            rowMap.put(this.readColList.get(j),null); //列值
                        }
                    }else{
                        rowMap.put(this.readColList.get(j),null); //列值
                    }
                }
                this.fieldValueList.add(rowMap);
            }
        }
    }

    private Object getCellValue(HSSFCell cell){
        int type=cell.getCellType();
        if(type==HSSFCell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }else if(type==HSSFCell.CELL_TYPE_NUMERIC){
            return cell.getNumericCellValue();
        }else if(type==HSSFCell.CELL_TYPE_FORMULA){
            return cell.getStringCellValue();
        }else if(type==HSSFCell.CELL_TYPE_ERROR){
            return cell.getErrorCellValue();
        }else if(type==HSSFCell.CELL_TYPE_BOOLEAN){
            return cell.getBooleanCellValue();
        }else if(type==HSSFCell.CELL_TYPE_BLANK){
            return "";
        }
        return "";
    }

    private HSSFRow getRow(int index){
        return this.sheet.getRow(index);
    }

    private short[] getReadColNum(){
        if(this.readColSize<=0)return null;
        short[] colNum=new short[this.readColSize];
        this.initializeShortArr(colNum);
        if(this.readColList==null){
            this.readColList=new ArrayList();
            for(short i=0;i<this.readColSize;i++){
                this.readColList.add("COL"+i);
                colNum[i]=i;
            }
        }else{
            HSSFRow row=this.getRow(this.fieldRowIndex);
            String titleCell=null;
            String titleList=null;
            for(short i=0;i<this.readColSize;i++){
                titleList=(String)this.readColList.get(i);
                for(short j=0;j<row.getLastCellNum();j++){
                    HSSFCell cell=row.getCell(j);
                    titleCell=cell.getStringCellValue();
                    if(titleCell!=null&&titleCell.equals(titleList)){
                        colNum[i]=j;
                    }
                }
            }
        }
        return colNum;
    }

    private void initializeShortArr(short arr[]){
        if(arr!=null){
            for(int i=0;i<arr.length;i++){
                arr[i]=-1;
            }
        }
    }

}
